import os
import tkinter as tk
from tkinter import filedialog, ttk
import pandas as pd
from pathlib import Path
import re
import chardet
import subprocess

# 配置文件路径（使用绝对路径避免路径问题）
CONFIG_FILE = os.path.join(os.path.dirname(os.path.abspath(__file__)), "last_folder.conf")

class ExcelSearchTool:
    def __init__(self, root=None):
        self.root = root or tk.Tk()
        self.root.title("Excel和CSV多文件搜索工具")
        self.root.geometry("1200x800")
        
        # 初始化属性
        self.selected_folder = ""
        self.file_path_cache = {}  # 缓存文件路径，避免重复查找
        
        # 加载上次文件夹
        self.load_last_folder()
        
        # 创建界面
        self.create_widgets()
        self.bind_events()
        
        # 初始化状态
        self.update_folder_label()

    def create_widgets(self):
        # 主框架
        main_frame = ttk.Frame(self.root, padding=10)
        main_frame.pack(fill=tk.BOTH, expand=True)
        
        # 文件夹选择区
        folder_frame = ttk.LabelFrame(main_frame, text="选择文件夹", padding=10)
        folder_frame.pack(fill=tk.X, pady=5)
        
        self.folder_label = ttk.Label(folder_frame, text="尚未选择文件夹", 
                                    font=('微软雅黑', 10), anchor=tk.W)
        self.folder_label.pack(side=tk.LEFT, padx=5, fill=tk.X, expand=True)
        
        ttk.Button(folder_frame, text="选择文件夹", command=self.select_folder, 
                 width=15).pack(side=tk.RIGHT, padx=5)
        
        # 搜索区
        search_frame = ttk.LabelFrame(main_frame, text="搜索内容", padding=10)
        search_frame.pack(fill=tk.X, pady=5)
        
        ttk.Label(search_frame, text="关键字:", font=('微软雅黑', 10))\
            .pack(side=tk.LEFT, padx=5)
        
        self.search_entry = ttk.Entry(search_frame, width=50, font=('微软雅黑', 10))
        self.search_entry.pack(side=tk.LEFT, padx=5, fill=tk.X, expand=True)
        self.search_entry.bind("<Return>", lambda e: self.search_keyword())
        
        ttk.Button(search_frame, text="搜索", command=self.search_keyword, 
                 width=15).pack(side=tk.LEFT, padx=5)
        
        # 结果显示区
        result_frame = ttk.LabelFrame(main_frame, text="搜索结果", padding=10)
        result_frame.pack(fill=tk.BOTH, expand=True)
        
        # Treeview组件
        self.tree = ttk.Treeview(result_frame, 
                               show="headings", 
                               selectmode="browse",
                               columns=("file", "sheet", "row", "data"))
        
        # 列配置
        self.tree.column("file", width=200, anchor=tk.W)
        self.tree.column("sheet", width=100, anchor=tk.W)
        self.tree.column("row", width=80, anchor=tk.CENTER)
        self.tree.column("data", width=800, anchor=tk.W)
        
        # 表头
        self.tree.heading("file", text="文件名", anchor=tk.W)
        self.tree.heading("sheet", text="工作表", anchor=tk.W)
        self.tree.heading("row", text="行号", anchor=tk.CENTER)
        self.tree.heading("data", text="数据内容", anchor=tk.W)
        
        # 滚动条
        vsb = ttk.Scrollbar(result_frame, orient=tk.VERTICAL, command=self.tree.yview)
        hsb = ttk.Scrollbar(result_frame, orient=tk.HORIZONTAL, command=self.tree.xview)
        self.tree.configure(yscrollcommand=vsb.set, xscrollcommand=hsb.set)
        
        self.tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
        vsb.pack(side=tk.RIGHT, fill=tk.Y)
        hsb.pack(side=tk.BOTTOM, fill=tk.X)
        
        # 进度条
        progress_frame = ttk.LabelFrame(main_frame, text="查询进度", padding=10)
        progress_frame.pack(fill=tk.X, pady=5)
        
        self.progress_label = ttk.Label(progress_frame, text="", font=('微软雅黑', 10))
        self.progress_label.pack(fill=tk.X, padx=5)
        
        self.progress_bar = ttk.Progressbar(progress_frame, mode='determinate')
        self.progress_bar.pack(fill=tk.X, padx=5, pady=5)
        
        # 状态栏
        self.status_bar = ttk.Label(main_frame, text="就绪", relief=tk.SUNKEN, 
                                  anchor=tk.W, font=('微软雅黑', 9), padding=5)
        self.status_bar.pack(fill=tk.X, pady=5)
        
        # 右键菜单
        self.context_menu = tk.Menu(self.root, tearoff=0)
        self.context_menu.add_command(label="打开文件", command=self.open_file)
        self.context_menu.add_command(label="打开文件所在文件夹", command=self.open_folder)

    def bind_events(self):
        self.tree.bind("<Button-3>", self.on_right_click)  # 右键事件
        self.tree.bind("<Double-1>", self.on_double_click)  # 双击事件

    def select_folder(self):
        folder = filedialog.askdirectory()
        if folder:
            self.selected_folder = folder
            self.save_last_folder(folder)
            self.update_folder_label()
            self.file_path_cache = {}  # 清空路径缓存

    def update_folder_label(self):
        if self.selected_folder:
            folder_name = Path(self.selected_folder).name
            self.folder_label.config(text=f"已选择文件夹: {folder_name}")
        else:
            self.folder_label.config(text="尚未选择文件夹")

    # -------------------- 路径管理 --------------------
    def get_file_path(self, file_name):
        """获取文件完整路径（带缓存）"""
        if file_name in self.file_path_cache:
            return self.file_path_cache[file_name]
            
        for root, _, files in os.walk(self.selected_folder):
            if file_name in files:
                full_path = os.path.join(root, file_name)
                self.file_path_cache[file_name] = full_path
                return full_path
        return None

    def save_last_folder(self, folder):
        """保存上次文件夹路径"""
        try:
            with open(CONFIG_FILE, 'w', encoding='utf-8') as f:
                f.write(folder)
        except Exception as e:
            self.status_bar.config(text=f"保存路径失败: {str(e)}")

    def load_last_folder(self):
        """加载上次文件夹路径"""
        if os.path.exists(CONFIG_FILE):
            try:
                with open(CONFIG_FILE, 'r', encoding='utf-8') as f:
                    self.selected_folder = f.read().strip()
            except Exception as e:
                self.status_bar.config(text=f"加载路径失败: {str(e)}")

    # -------------------- 事件处理 --------------------
    def on_right_click(self, event):
        """右键菜单事件"""
        item = self.tree.identify('item', event.x, event.y)
        if item:
            self.tree.selection_set(item)
            self.context_menu.post(event.x_root, event.y_root)

    def on_double_click(self, event):
        """双击事件"""
        self.open_file()

    def open_file(self):
        """打开选中文件"""
        selected = self.tree.selection()
        if not selected:
            self.status_bar.config(text="请先选择一个搜索结果")
            return
            
        file_name = self.tree.item(selected[0])['values'][0]
        full_path = self.get_file_path(file_name)
        
        if not full_path or not os.path.exists(full_path):
            self.status_bar.config(text="文件不存在或路径错误")
            return
            
        try:
            if os.name == 'nt':  # Windows
                os.startfile(full_path)
            else:  # 其他系统
                subprocess.run(['open', full_path], check=True)
            self.status_bar.config(text=f"已打开文件: {file_name}")
        except Exception as e:
            self.status_bar.config(text=f"打开文件失败: {str(e)}")

    def open_folder(self):
        """打开文件所在文件夹"""
        selected = self.tree.selection()
        if not selected:
            self.status_bar.config(text="请先选择一个搜索结果")
            return
            
        file_name = self.tree.item(selected[0])['values'][0]
        full_path = self.get_file_path(file_name)
        
        if not full_path:
            self.status_bar.config(text="文件路径错误")
            return
            
        folder_path = os.path.dirname(full_path)
        if not os.path.exists(folder_path):
            self.status_bar.config(text="文件夹不存在")
            return
            
        try:
            if os.name == 'nt':
                os.startfile(folder_path)
            else:
                subprocess.run(['open', folder_path], check=True)
            self.status_bar.config(text=f"已打开文件夹: {folder_path}")
        except Exception as e:
            self.status_bar.config(text=f"打开文件夹失败: {str(e)}")

    # -------------------- 搜索功能 --------------------
    def search_keyword(self):
        self.tree.delete(*self.tree.get_children())
        keyword = self.search_entry.get().strip().lower()
        
        if not keyword:
            self.status_bar.config(text="请输入搜索关键字")
            return
            
        if not self.selected_folder:
            self.status_bar.config(text="请先选择文件夹")
            return
            
        files = self.get_all_files()
        total = len(files)
        self.progress_bar.config(maximum=total, value=0)
        found = 0
        
        try:
            for idx, file_path in enumerate(files, 1):
                file_name = Path(file_path).name
                self.update_progress(idx, total, file_name)
                
                try:
                    if file_path.lower().endswith(('.xlsx', '.xls')):
                        self.process_excel(file_path, file_name, keyword, found)
                    elif file_path.lower().endswith('.csv'):
                        self.process_csv(file_path, file_name, keyword, found)
                        
                except Exception as e:
                    self.status_bar.config(text=f"处理 {file_name} 失败: {str(e)}")
                
                self.root.update()
            
            self.status_bar.config(text=f"搜索完成，找到 {found} 个匹配项")
            
        except Exception as e:
            self.status_bar.config(text=f"搜索中断: {str(e)}")
        finally:
            self.progress_bar.config(value=total)

    def get_all_files(self):
        """获取所有Excel/CSV文件（带完整路径）"""
        files = []
        for root, _, fs in os.walk(self.selected_folder):
            for f in fs:
                if f.lower().endswith(('.xlsx', '.xls', '.csv')):
                    files.append(os.path.join(root, f))
        return files

    def process_excel(self, file_path, file_name, keyword, found):
        excel = pd.ExcelFile(file_path)
        for sheet_name in excel.sheet_names:
            try:
                df = excel.parse(sheet_name, dtype=str)
                for row_idx, row in df.iterrows():
                    clean_row = ' '.join(str(v).lower() for v in row).replace('\W', ' ')
                    if keyword in clean_row:
                        self.add_result(file_name, sheet_name, row_idx+1, row, found)
                        found += 1
            except Exception as e:
                print(f"工作表 {sheet_name} 错误: {str(e)}")

    def process_csv(self, file_path, file_name, keyword, found):
        df = self.read_csv(file_path)
        if not df.empty:
            for row_idx, row in df.iterrows():
                clean_row = ' '.join(str(v).lower() for v in row).replace('\W', ' ')
                if keyword in clean_row:
                    self.add_result(file_name, "CSV", row_idx+1, row, found)
                    found += 1

    def read_csv(self, file_path):
        try:
            with open(file_path, 'rb') as f:
                encoding = chardet.detect(f.read(10000))['encoding']
            return pd.read_csv(file_path, encoding=encoding, sep=None, 
                             dtype=str, on_bad_lines='skip', engine='python')
        except:
            return pd.DataFrame()

    def add_result(self, file_name, sheet, row, data, found):
        tag = 'even' if found % 2 else 'odd'
        self.tree.insert('', 'end', values=(
            file_name,
            sheet,
            f"第{row}行",
            " | ".join(str(v) for v in data)
        ), tags=(tag,))

    def update_progress(self, idx, total, file_name):
        self.progress_bar.config(value=idx)
        self.progress_label.config(text=f"处理中: {file_name} ({idx}/{total})")


if __name__ == "__main__":
    app = ExcelSearchTool()
    app.root.mainloop()
